#!/usr/bin/env python
# Copyright(c) 2010 Nuttachat Wisittipanit. All rights reserved.

from mahbo.lib import (PHD,SeqInfo,FastaSeqInfo,)
from mahbo.information.database.utility.dbinterface import DatabaseInterface
from mahbo.information.dataprocess.report.featurereport import FeatureReport 
from mahbo.information.dataprocess.report.svmreport import SVMReport
from mahbo.information.dataprocess.report.mstatreport import MSTATReport
from mahbo.information.dataprocess.report.formalreport import FormalReport
from mahbo.utility.utility import (DataPrePackage,RESULTS,)

from minings import (Datap,SVMpm,Mpm,)
from minings.classification.svm.svm import ProcessSVM
from minings.factory.datafactory import DataFactory
from minings.stat.mstat import ProcessMetastats
from minings.utility.utility import (DatasetTools,RUN,)
from gutility.gutility import GPF

from copy import deepcopy
import os,numpy,orange,MySQLdb,xlwt,xlrd,cPickle,string,interval
import re
import psyco
psyco.full() #Speeding Up Python

Abr = PHD.Abr.main


class GUTSEQ(DatabaseInterface):
    
    def __init__(self,**kwargs):        
        self.maindbname                 = 'gutseq'
        self.maintable                  = 'gutseq'
        self.maintablem                 = 'gutseqm'
        self.pthreshold                 = 0.05
        self.window                     = 4
        self.flow                       = None #FLOW instance
        #xcel file for writing
        self.__dict__.update(kwargs)
        DatabaseInterface.__init__(self,self.maindbname,True)
        self.addConnect('gut')
        self.addConnect('gutmtps')
        self.maindataoption             = Datap.Option.fix
        self.mainpermremovelist         = ['Polyp','Diverticulitis','Normal Pouch','Pouchitis','Alcoholic','ALD','Non ALD']
        self.keeplist                   = ['Crohn','Ulcerative Colitis','Healthy Control']
        self.mainfixedcls               = 'Healthy Control'
        #Start
        self.rawdata                    = {}
        #Table properties        
        self.mtname                     = '%s_%s' % (self.maindbname,self.window,)
        self.mtnamem                    = '%sm' % (self.mtname,)
        self.ftableName                 = '%s_fq%s' % (self.maindbname,self.window,)
        self.mainTables                 = (self.mtname,self.mtnamem,self.ftableName,)
        self.getXcelProperty()
    
    def getXcelProperty(self):
        #XCel Read properties
        #IBD_All_RDP10_table_X -> MTPS data of every RDP level, Sample names has Fingerprint ID
        self.output_folder      = PHD.gutmtps.outseq
        self.ioutput_file       = os.path.join(self.output_folder,'info_gutseq.xls')
        self.svmoutput_file     = os.path.join(self.output_folder,'svm_gutseq.xls')
        self.mstatoutput_file   = os.path.join(self.output_folder,'mstat_gutseq.xls')
        self.foutput_file       = os.path.join(self.output_folder,'feature_gutseq.xls')
        self.moutput_file       = os.path.join(self.output_folder,'formal_gutseq.xls')
        self.col_width          = 5000
        #Get CD-HIT property files
        self.seq_folder         = PHD.gutmtps.dataseq
        self.fasta_file         = os.path.join(self.seq_folder,'ibdmtps_raw.fasta')
        self.test_file          = os.path.join(self.seq_folder,'test.fasta')
        self.test1_file         = os.path.join(self.seq_folder,'test1.fasta')
        self.data_file          = os.path.join(self.output_folder,'%s.txt' % self.mtname)
        #orange table properties
        self.preSampleD         = []
        self.nameIndexD         = []
        #[0,1,2,1] Disease in index format
        self.nameIndex          = []
        #{'Dys100_19':Ulcerative Colitis, ..}
        self.name2class         = {}    
        #{"Crohns":0,"Crohns-ileitis":1,"Diverticulitis":2,
        # "Polyp" :3,"Pouchitis":4     ,"Ulcerative colitis":5,"Healthy Control":6}
        self.class2index        = {}    
        #["Crohns","Crohns-ileitis","Diverticulitis","Polyp",
        #"Pouchitis","Ulcerative colitis","Healthy Control"]
        self.classList          = []
                    
    def doMain(self):
        isDataExist             = True
        #Check table exist or not
        
        for i in self.mainTables:
            try:
                self.t.query("""select * from %s""" % i)
            except (MySQLdb.ProgrammingError,):
                isDataExist = False
                break
        if not isDataExist:
            self.constructMainTables()
        self.getRawdataMySQL()
    
    def writeDatasetInformation(self):
        book            = xlwt.Workbook()              
        #Add sheet 'RDP LEVEL 1'               
        #.maindata -> data for 'rdp8level1' or 'rdp10level6' 
        data            = self.t.query("""select * from %s
                                       """ % self.maindbname)     
        data            = DatasetTools.finalBrush(data)           
        #name -> 'RDP LEVEL 1'
        name            = self.maindbname.upper()
        sheet           = book.add_sheet(name)
        for i in range(6):
            sheet.col(i).width = self.col_width
        DBName          = self.maindbname
        #I need 'rdp8level1_ileum','rdp8level1_colon','rdp8level1_sigmoid',
        #       'rdp8level1_lumen','rdp8level1_entire'
        #siteinfo -> {'ileum': {'Crohn': 10,'Ulcerative Colitis': 12,'Healthy Control': 15,'Total': 20}, ..}
        #Collect Information 
        numfeatures     = len(data.domain.attributes)
        siteinfo        = {}
        for site in PHD.site.LIST:
            #sited -> {'Crohn': 10,'Ulcerative Colitis': 12,'Healthy Control': 15,'Total': 20}
            sited               = {}
            #site -> 'ileum',sitename -> 'gutmtpslhpcr_ileum'                    
            sitename            = '%s_%s' % (DBName,site)
            sitedata            = self.t.query("""select * from %s
                                               """ % sitename)            
            sitedata            = DatasetTools.finalBrush(sitedata)
            for cls,number in zip(sitedata.domain.classVar.values,
                              orange.Distribution(sitedata.domain.classVar,sitedata)):
                sited[cls]      = number
            sited['Total']      = len(sitedata)
            siteinfo[site]      = deepcopy(sited)                
        #Write Information
        self.cur_col    = 0
        self.cur_row    = 0
        sheet.row(self.cur_row).write(self.cur_col,'Disease/Location')
        row             = self.cur_row + 1
        for disease in PHD.disease.LIST:
            sheet.row(row).write(self.cur_col,disease)
            row         += 1
        sheet.row(row).write(self.cur_col,'Total')
        self.cur_col    = 1
        self.cur_row    = 0
        row             = self.cur_row 
        for i in PHD.site.LIST:            
            #i -> 'ileum'
            usesiteName     = i[0].upper() + i[1:]        
            sheet.row(self.ri()).write(self.cur_col,usesiteName)
            dist            = PHD.disease.LIST + ('Total',)
            for j in dist:
                number      = siteinfo[i][j]
                sheet.row(self.ri()).write(self.cur_col,number)
            #Set row back to the same state
            self.cur_row    = row
            #Increate Column by 1
            self.ci()
        sheet.row(6).write(0,'Number of Features')
        sheet.row(6).write(1,numfeatures)
        book.save(self.ioutput_file)
    
    def clearMainTables(self):
        for i in self.mainTables:
            try:
                self.RD.query("""drop table %s
                              """ % i)
            except (MySQLdb.OperationalError,):
                #If the table does not exist
                pass
    
    def constructMainTables(self):
        self.clearMainTables()
        #raw ibdmtps fasta file
        Pwhite      = re.compile('\s')
        sdata_f     = open(self.fasta_file,'r')
#        sdata_f     = open(self.test_file,'r')
#        sdata_f     = open(self.test1_file,'r')        
        sdata       = sdata_f.read()
        self.fastaL = []
        NNindex     = 0
        k           = 1
        while k: 
            Aindex  = sdata.find('>',NNindex)
            if Aindex == -1:
                break            
            Nindex  = sdata.find('\n',Aindex+1)
            xname   = sdata[Aindex+1:Nindex]
            xlist   = xname.split('-')
            subname = xlist[0]
            sname   = xlist[1]
            NNindex = sdata.find('\n',Nindex + 1)
            seq     = sdata[Nindex+1:NNindex]
            seq     = seq.strip()
            seq     = re.sub(Pwhite,'',seq) #Only pure sequence
            fsi     = FastaSeqInfo(sname    = sname     ,
                                   seq      = seq       ,
                                   subname  = subname   ,
                                   )
            self.fastaL.append(fsi)
        #Collect seq having the same sample name
        #.fsiHash -> {'Dys105A_38:list of FastSeqInfo instance, ..}
        self.fsiHash    = {}
        for i in self.fastaL:
            try:
                self.fsiHash[i.sname]
            except (KeyError,):
                self.fsiHash[i.sname]   = []
            self.fsiHash[i.sname].append(i)
        #sifList -> {'Dys100_19': SeqInfo instance, ..}
        self.sifHash    = {}
        #Get valid SampleName from table rdp8level1 from gutmtps database
        self.adb['gutmtps'].query("""select * from %s
                                  """ % ('rdp8level1'))
#        self.adb['gutmtps'].query("""select * from %s
#                                  """ % ('rdp8level1_entire'))
        row     = self.adb['gutmtps'].cursor.fetchall()
        for i in row:
            cDisease    = i['c$Disease']
            #mtpsSampleName -> ['Dys100_19','Dys100_37','Dys100_4',..]            
            mSampleName = i['m$SampleName']
            mClassX     = i['m$ClassX']
            mClass1     = i['m$Class1']
            try:
                self.sifHash[mSampleName]
            except (KeyError,):
                self.sifHash[mSampleName]   = SeqInfo(SampleName    = mSampleName   ,
                                                      Disease       = cDisease      ,
                                                      Class1        = mClass1       ,
                                                      ClassX        = mClassX       ,
                                                      )                    
        #self.confea -> ['ATC','ATT',..]
        #contain every feature 
        self.confea = []
        #self.rseq -> {'Dys100_19': {'TCG':3,'AAA':4}, ..}
        self.rseq   = {}        
        #Find the frequency
        cnt         = 1
        for i in self.fsiHash:
            if i not in self.sifHash.keys():
                continue
            Disease         = self.sifHash[i].Disease
            print '%s : %s' % (i,cnt)
            cnt += 1            
            try:
                self.classList.index(Disease)
            except (ValueError,):
                self.classList.append(Disease)
            #i -> 'Dys105A_38'
            self.rseq[i]    = {}
            for j in self.fsiHash[i]:
                #j -> FastSeqInfo instance
                seq         = j.seq
                #Do it now
                for k in range(len(seq)):
                    #g -> 'ATC'
                    g   = seq[k:k+self.window]
                    if len(g) < self.window:
                        break
                    try:
                        self.rseq[i][g]
                    except (KeyError,):
                        self.rseq[i][g] = 0 
                    self.rseq[i][g] += 1
                    try:
                        self.confea.index(g)
                    except (ValueError,):
                        self.confea.append(g)        
        self.confea.sort()
        #Create frequency table
        self.createFrequencyTable()
        #[Dys100_19,Dys100_37,..]
        self.sampleName = []
        #.preSample -> [[1,2,1,0,0,1,2,..], ..]
        self.preSample  = []
        #Concatenate feature
        for i in self.rseq:
            #i -> 'Dys100_19'
            self.sampleName.append(i)
            #local -> [1,2,1,0,0,1,2,..]
            local   = []
            for j in self.confea:
                #j -> 'ATC'
                try:
                    count = self.rseq[i][j]
                except (KeyError,):
                    #No feature exist, assign 0
                    count = 0
                local.append(count)
            self.preSample.append(local)            
#        print self.sampleName
#        print self.confea
#        for i in self.preSample:
#            print i
#        print self.classList
        self.createDataFile()
#        self.manualTable()
        
    def manualTable(self):
        #'A_0' float,'A_1' float,'A_2' float
        #c$Disease enum('Healthy Control','Ulcerative Colitis','Crohn'),
        #m$Class1 char(200),
        #m$ClassX char(200),
        #m$SampleName char(200)        
        #Create MySQL columns for float features
        #self.confea -> ['ACA', 'ACT', 'ATC',..]
        #tranform to "'ACA' float,'ACT' float,'ATC' float,"
        #g -> ['ACA float,','ACT float,',..]
        floatList       = ['%s float,' % u for u in self.confea]
        floatFeature    = ''.join(floatList)
        #c$Disease
        diseaseList     = string.join(self.classList,"','")
        diseaseFeature  = "c$Disease enum('%s')," % diseaseList
        #m$Class1
        Class1Feature   = 'm$Class1 char(200),'
        ClassXFeature   = 'm$ClassX char(200),'
        SampleFeature   = 'm$SampleName char(200)'
        #Combine
        allFeature      = '%s%s%s%s%s' % (floatFeature,diseaseFeature,Class1Feature,ClassXFeature,SampleFeature,)
        #self.mtname -> table name for particular N-mer        
        self.RD.query("""
                create table %s
                (
                    %s
                )
                ENGINE = MYISAM
                """ % ( self.mtname,allFeature,))
        #Inseart data
        #Generate ("%s","%s",..)
        ps      =  "'%s'"
        psLen   = len(floatList) + 4
        #psGen -> "'%s','%s',.."
        psGen   = [ps for i in range(psLen)] #@UnusedVariable
        psGenA  = string.join(psGen,',')
        psGenB  = '(%s)' % psGenA
        for i in range(len(self.preSample)):            
            sampleName  = self.sampleName[i]
            Disease     = self.sifHash[sampleName].Disease
            Class1      = self.sifHash[sampleName].Class1
            ClassX      = self.sifHash[sampleName].ClassX            
            tall        = tuple(self.preSample[i] + [Disease,Class1,ClassX,sampleName])
            queryText   = 'insert into %s values %s' % (self.mtname,psGenB,)
            self.RD.query(queryText % tall)
#        data        = self.t.query("""select * from %s""" % (self.mtname,))
#        fdata_temp  = os.path.join(self.output_folder,'%s_db.txt' % self.mtname)
#        data.save(fdata_temp)
#        databack    = orange.ExampleTable(fdata_temp)
#        print databack.domain.classVar
    
    def createFrequencyTable(self):
        #'fq_nmer_4        
        self.RD.query("""
                create table %s
                (
                    %s int NOT NULL AUTO_INCREMENT,%s CHAR(200),%s INT,
                    PRIMARY KEY(%s)
                )
                ENGINE = MYISAM
                """ % ( self.ftableName ,
                        'fid'           ,
                        'feature'       ,
                        'count'         ,
                        'fid'           ,))
        #.fseq -> {'TCG':14,'AAA':9,..}
        self.fseq       = {}
        self.fseq_sort  = []
        #self.rseq -> {'Dys100_19': {'TCG':3,'AAA':4}, ..}
        for i in self.rseq:
            for j in self.rseq[i]:
                try:
                    self.fseq[j]
                except (KeyError,):
                    self.fseq[j]    = 0
                self.fseq[j] += 1
        for i in self.fseq:
            self.fseq_sort.append((self.fseq[i],i))
        self.fseq_sort.sort(reverse = True)
        for i in self.fseq_sort:
            feature = i[1]
            count   = i[0]
            self.RD.query("""insert into %s (%s,%s) values ("%s","%s")
                          """ % (self.ftableName    ,
                                 'feature'          ,
                                 'count'            ,
                                 feature            ,
                                 count              ,
                                 ))
    
    def createDataFile(self):
        dfile = open(self.data_file,'w') 
        for i in range(len(self.confea)):
            dfile.write('%s\t' % self.confea[i])
        dfile.write('c#Disease\t')
        dfile.write('m#Class1\t')
        dfile.write('m#ClassX\t')
        dfile.write('m#SampleName\n')
        for i in range(len(self.preSample)):
            #.preSample[i] -> [0, 0, 1, 0, 0, 0, 0, 0,..]
            sampleName  = self.sampleName[i]
            Disease     = self.sifHash[sampleName].Disease
            Class1      = self.sifHash[sampleName].Class1
            ClassX      = self.sifHash[sampleName].ClassX
            for j in self.preSample[i]:
                dfile.write('%.3f\t' % j)
            dfile.write('%s\t' % Disease)
            dfile.write('%s\t' % Class1)
            dfile.write('%s\t' % ClassX)
            dfile.write('%s\n' % sampleName)             
        dfile.close()
#        datafile    = orange.ExampleTable(self.data_file)
#        print datafile.domain.classVar.values
        
    def getRawdataMySQL(self):
        self.doAll_bool = False
        tempraw         = []
        self.RD.query("""SELECT * FROM %s
                      """ % (self.RD.tab_info.tablename,))
        row             = self.RD.cursor.fetchall() 
        if row not in (None,()):
            #Check whether I have everything ready to construct a datapackage
            for i in row:
                #dataname -> 'gutmtpslhpcr_Entire'
                dataname    = i[self.RD.tab_info.name]
                info        = cPickle.loads(i[self.RD.tab_info.info_obj])
                tempraw.append((dataname,info))
                try:
                    self.RD.query("""DESC %s
                                  """ % (dataname,))
                except (MySQLdb.ProgrammingError,):
                    #If atleast one dataname does not exist, trigger doAll
                    self.doAll_bool = True
                    break
        else:
            self.doAll_bool = True 
        if self.doAll_bool:
            self.doAll()
        else:
            for i in tempraw:
                tempdata            = self.t.query("""SELECT * FROM %s""" % i[0])
                tempdata            = DatasetTools.finalBrush(tempdata)
                self.rawdata[i[0]]  = GutMTPSDataPackage(fullname       = i[0]                  ,
                                                         data           = tempdata              ,
                                                         dataoption     = i[1].dataoption       ,
                                                         permremovelist = i[1].permremovelist   ,
                                                         keeplist       = i[1].keeplist         ,
                                                         fixedcls       = i[1].fixedcls         ,
                                                         matrix         = i[1].matrix           ,
                                                         pair           = i[1].pair             ,
                                                         run            = i[1].run              ,
                                                         Class_X        = i[1].Class_X          ,
                                                         Class_1        = i[1].Class_1          ,
                                                         Length         = i[1].Length           ,
                                                         )
    
    def doAll(self):
        ##########################
        ###Start Gathering Data###
        ##########################
        self.keyRank                    = []
        self.getAlldata()
    
    def getAlldata(self):
        #Get 'Entire','Ileum','Colon','Sigmoid','Lumen'
        xname                  = 'Entire'
        d_entire               = self.t.query("""SELECT * FROM %s WHERE ( (%s in ("%s","%s","%s","%s")) and (%s!="%s") ) 
                                                 """ % (self.maintable, 'm$ClassX'     ,'Ileum'    ,'Colon'        ,
                                                                        'Sigmoid'       ,'Lumen'    ,
                                                                        'm$Class1'      ,''         ,
                                                                        ))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_entire              ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,        
                                 Class_X            = None                      ,Class_1    = None                  ,Length     = len(d_entire)         ,)                                 
        self.keyRank.append(fullname)
        #Get Ileum with
        #Class_X = Ileum and Class_1 != ''
        xname                  = 'Ileum'
        d_ileum                = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.maintable, 'm$ClassX'  ,'Ileum'    ,'m$Class1'     ,''     ,
                                                        ))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_ileum               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 Class_X            = 'Ileum'                   ,Class_1    = None                  ,Length             = len(d_ileum)  ,)                                 
        self.keyRank.append(fullname)
        #Get Colon
        xname                  = 'Colon'
        d_colon                = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.maintable, 'm$ClassX'     ,'Colon'         ,
                                                                        'm$Class1'     ,''              ,
                                                                        ))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_colon               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 Class_X            = None                      ,Class_1    = None                  ,Length     = len(d_colon)          ,)                                           
        self.keyRank.append(fullname)
        #Get Sigmoid
        xname                  = 'Sigmoid'
        d_sigmoid              = self.t.query("""SELECT * FROM %s WHERE (%s in ("%s")) and (%s!="%s")
                                                 """ % (self.maintable, 'm$ClassX'      ,'Sigmoid'      ,'m$Class1'     ,''             ,))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_sigmoid             ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 Class_X            = None                      ,Class_1    = None                  ,Length     = len(d_sigmoid)        ,)                                                                           
        self.keyRank.append(fullname)
        #Get Lumen
        xname                  = 'Lumen'
        d_lumen                = self.t.query("""SELECT * FROM %s WHERE %s="%s"
                                                 """ % (self.maintable,    'm$ClassX'  ,'Lumen'     ,))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data       = d_lumen               ,dataoption = self.maindataoption   ,
                                 permremovelist     = self.mainpermremovelist   ,keeplist   = self.keeplist         ,fixedcls   = self.mainfixedcls     ,
                                 Class_X            = 'Lumen'                   ,Class_1    = None                  ,Length     = len(d_lumen)          ,)                                                                               
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Ileum of C,UC,HC
        xname                  = 'Ileum_Lumen'
        d_ileum_lumen          = self.t.query("""SELECT * FROM %s WHERE ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                         (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                             
                                                 """ % (self.maintablem,    'm$ClassX'      ,'Ileum'    ,'m$Class1'     ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Ileum'   ,'Ulcerative Colitis_Ileum' ,'Healthy Control_Ileum'    ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen' ,'Healthy Control_Lumen'    ,))                                                  
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data   = d_ileum_lumen             ,dataoption  = Datap.Option.pair    ,
                                 keeplist           = ['Crohn_Ileum'               ,'Crohn_Lumen'                   ,
                                                       'Ulcerative Colitis_Ileum'  ,'Ulcerative Colitis_Lumen'      ,
                                                       'Healthy Control_Ileum'     ,'Healthy Control_Lumen'         ,
                                                       ],
                                 pair               = [('Crohn_Ileum','Crohn_Lumen')                                ,
                                                       ('Ulcerative Colitis_Ileum','Ulcerative Colitis_Lumen')      ,
                                                       ('Healthy Control_Ileum','Healthy Control_Lumen')            ,
                                                       ],                                                                                                                                                                                    
                                 Length             = len(d_ileum_lumen)        ,)                                              
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Colon of C,UC,HC
        xname                  = 'Colon_Lumen'
        d_colon_lumen          = self.t.query("""SELECT * FROM %s WHERE ( ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                            (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                            )
                                                 """ % (self.maintablem,    'm$ClassX'      ,'Colon'    ,'m$Class1'         ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Colon'   ,'Ulcerative Colitis_Colon'     ,'Healthy Control_Colon'    ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen'     ,'Healthy Control_Lumen'    ,))                                                   
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                  ,data   = d_colon_lumen             ,dataoption  = Datap.Option.pair    ,                                                     
                                 keeplist           = ['Crohn_Colon'                ,'Crohn_Lumen'                  ,
                                                       'Ulcerative Colitis_Colon'   ,'Ulcerative Colitis_Lumen'     ,
                                                       'Healthy Control_Colon'      ,'Healthy Control_Lumen'        ,
                                                       ],
                                 pair               = [('Crohn_Colon','Crohn_Lumen')                                ,
                                                       ('Ulcerative Colitis_Colon','Ulcerative Colitis_Lumen')      ,
                                                       ('Healthy Control_Colon','Healthy Control_Lumen')            ,
                                                       ],                                                                                                                                                                                    
                                 Length             = len(d_colon_lumen)        ,)
        self.keyRank.append(fullname)
        #Get mucosa vs lumen of Sigmoid of C,UC,HC
        xname                  = 'Sigmoid_Lumen'
        d_sigmoid_lumen        = self.t.query("""SELECT * FROM %s WHERE ( ((%s in ("%s") and %s!="%s") or (%s in ("%s"))) and   
                                                                              (%s in ("%s","%s","%s","%s","%s","%s")) 
                                                                            ) 
                                                 """ % (self.maintablem,    'm$ClassX'      ,'Sigmoid'  ,'m$Class1'         ,''                         ,
                                                                            'm$ClassX'      ,'Lumen'    ,
                                                                            'c$Disease'     ,
                                                                            'Crohn_Sigmoid' ,'Ulcerative Colitis_Sigmoid'  ,'Healthy Control_Sigmoid'  ,
                                                                            'Crohn_Lumen'   ,'Ulcerative Colitis_Lumen'    ,'Healthy Control_Lumen'    ,))
        fullname               = '%s_%s' % (self.maintable,xname)
        self.rawdata[fullname] = GutMTPSDataPackage(
                                 fullname           = fullname                      ,data   = d_sigmoid_lumen       ,dataoption  = Datap.Option.pair    ,
                                 keeplist           = ['Crohn_Sigmoid'              ,'Crohn_Lumen'                  ,
                                                       'Ulcerative Colitis_Sigmoid' ,'Ulcerative Colitis_Lumen'     ,
                                                       'Healthy Control_Sigmoid'    ,'Healthy Control_Lumen'        ,
                                                       ],
                                 pair               = [('Crohn_Sigmoid','Crohn_Lumen')                             ,
                                                       ('Ulcerative Colitis_Sigmoid','Ulcerative Colitis_Lumen')   ,
                                                       ('Healthy Control_Sigmoid','Healthy Control_Lumen')         ,
                                                       ],                                                                                                                                                                                    
                                 Length             = len(d_sigmoid_lumen)      ,)
        self.keyRank.append(fullname)
        #Record Data
        #self.maindbname -> 'gutmtps'
        #self.rdpDbName    -> 'rdp8level1'
        for i in self.keyRank:
            #i -> 'Lumen'
            fullname                    = i
            self.rawdata[i].data        = DatasetTools.finalBrush(self.rawdata[i].data)
            try:
                self.t.write(fullname,self.rawdata[i].data,overwrite = True)
            except (MySQLdb.OperationalError,):
                pass
            info        = INFO(fullname         = self.rawdata[i].fullname          ,
                               dataoption       = self.rawdata[i].dataoption        ,
                               permremovelist   = self.rawdata[i].permremovelist    ,
                               keeplist         = self.rawdata[i].keeplist          ,
                               fixedcls         = self.rawdata[i].fixedcls          ,
                               matrix           = self.rawdata[i].matrix            ,
                               pair             = self.rawdata[i].pair              ,
                               Class_X          = self.rawdata[i].Class_X           ,
                               Class_1          = self.rawdata[i].Class_1           ,                               
                               Length           = self.rawdata[i].Length            ,
                               )
            info_string = MySQLdb.escape_string(cPickle.dumps(info,2))
            #Determine to insert new or update the table
            self.RD.query("""select %s from %s where %s="%s"
                          """ % ( self.RD.tab_info.name,self.RD.tab_info.tablename,self.RD.tab_info.name,fullname))
            row         = self.RD.cursor.fetchone()
            if row not in (None,()):
                #if full name does exist,update
                self.RD.query("""update %s set %s="%s" where %s="%s"
                              """ % ( self.RD.tab_info.tablename,self.RD.tab_info.info_obj,info_string,
                                                                 self.RD.tab_info.name,fullname ) )
            else:
                self.RD.query("""insert into %s (%s,%s) values  ("%s","%s")
                              """ % ( self.RD.tab_info.tablename,self.RD.tab_info.name,self.RD.tab_info.info_obj,
                                                                 fullname,info_string ) )
                
    def ci(self):
        self.cur_col += 1
        #Increase and return current column
        return self.cur_col - 1
    
    def ri(self):
        self.cur_row += 1
        return self.cur_row - 1
    
    def processDataFactory(self):
        for i in self.rawdata:
            for j in self.rawdata[i].datapacages:
                #j -> DataPackage instance
                dname       = j.datasetname
                doDatafact  = True
                #Upload information for a dataset 'rdp10level1_Entire_Crohn_Healthy Control'
                self.RD.query("""select * from %s where %s="%s"
                              """ % (self.RD.tab_results.tablename      ,
                                     self.RD.tab_results.datasetname    ,
                                     self.F.datasetname                 ,))
                row         = self.RD.cursor.fetchone()
                if row in (None,()):
                    #If dataset do not exist,mark as freshy    -> True
                    freshy      = True
                else:        
                    freshy      = False
                    result_obj  = cPickle.loads(row[self.RD.tab_results.result_obj])
                    datafact    = result_obj.datafact   
                    if datafact == None:
                        doDatafact     = True
                    else:
                        #DataFactory instance exists
                        if self.flow.REdatafact:
                            #if flow.REdatafact -> True,redo it
                            doDatafact = True
                        else:
                            doDatafact = False
                if doDatafact:
                    df = DataFactory(datapackage = j                 ,                                                                                                       
                                     run         = RUN()             ,
                                     )
                    df.getDataPipeline()
                else:
                    continue
                if freshy:
                    #in anycase,if freshy -> True,do anew anyway (only record DataFactory instance)
                    result_obj          = RESULTS()
                    result_obj.datafact = df
                    result_obj_string   = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                    self.RD.query("""insert into %s (%s,%s) values  ("%s","%s")
                                  """ % (self.RD.tab_results.tablename      ,    
                                         self.RD.tab_results.datasetname    ,
                                         self.RD.tab_results.result_obj     ,
                                         dname                              ,
                                         result_obj_string                  ,))
                else:
                    #freshy -> False,doDatafact -> True,come in here
                    result_obj.datafact = df
                    result_obj_string   = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                    self.RD.query("""update %s set %s="%s" where %s="%s"
                                  """ % (self.RD.tab_results.tablename      ,
                                         self.RD.tab_results.result_obj     ,
                                         result_obj_string                  ,
                                         self.RD.tab_results.datasetname    ,
                                         dname                              ,))
    
    def gatherRecordInformation(self):
        self.dset    = {}
        self.dsort   = []
        self.nameabr = [] #List of abbrevation names
        #Initial running
        self.RD.query("""select * from %s
                      """ % self.RD.tab_results.tablename)
        row     = self.RD.cursor.fetchall()
        if row in (None,()):
            return
        #Gather information
        for i in row:
            datasetname             = i[self.RD.tab_results.datasetname] #'gut_Endo_Ileum__Crohn__Healthy Control'            
            #result_obj  -> RESULTS instance
            result_obj              = cPickle.loads(i[self.RD.tab_results.result_obj])
            self.dset[datasetname]  = result_obj
            self.dsort.append(datasetname)            
#            break
        self.dsort.sort()
        for i in self.dsort:
            nameAbr                 = self.getAbrName(i)
            self.nameabr.append(nameAbr)
    
    def processSVM(self):
        for i in range(len(self.dsort)):
            dname           = self.dsort[i]
            result_obj      = self.dset[dname]
            datafact        = result_obj.datafact
            results_svm     = result_obj.results_svm
            runSVM          = False
            if results_svm != None:
                #if reslts_svn exists,check flow.REsvm
                if self.flow.REsvm:
                    #if flow.REsvm is True,do it, one by one
                    runSVM  = True
                else:
                    continue
            else:
                runSVM      = True
            if runSVM:
                psvm = ProcessSVM(runLearner  = (SVMpm.Kern.RBF,)   ,
                                  drtn        = (
                                                 SVMpm.Drtn.NORM    ,
                                                 SVMpm.Drtn.B2UP    ,
                                                 )                  ,                            
                                  datafact    = datafact            ,
                                  )
                psvm.mainRun()
                #psvm.mainres -> RES instance
                result_obj.results_svm  = psvm.mainres
                result_obj_string       = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                self.RD.query("""update %s set %s="%s" where %s="%s"                                                                                    
                              """ % (self.RD.tab_results.tablename      ,  
                                     self.RD.tab_results.result_obj     ,
                                     result_obj_string                  ,
                                     self.RD.tab_results.datasetname    ,
                                     dname                              ,
                                     ))
    
    def processMSTAT(self):
        for i in range(len(self.dsort)):
            dname           = self.dsort[i]
            result_obj      = self.dset[dname]
            datafact        = result_obj.datafact
            results_svm     = result_obj.results_svm
            results_mstat   = result_obj.results_mstat
            runMSTAT        = False
            if results_mstat != None:
                if self.flow.REmstat == True:
                    runMSTAT    = True        
                else:
                    continue
            else:
                runMSTAT        = True
            if runMSTAT:
                pms = ProcessMetastats(runot            = Mpm.run.both  ,                                       
                                       pthreshold       = 0.08          , 
                                       datafact         = datafact      ,
                                       results_svm      = results_svm   ,
                                       results_mstat    = results_mstat ,                                      
                                       )
                pms.mainRun()
                #psvm.mainres -> RES instance
                result_obj.results_mstat    = pms.mainres
                result_obj_string           = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                self.RD.query("""update %s set %s="%s" where %s="%s"                                                                                    
                              """ % (self.RD.tab_results.tablename      ,  
                                     self.RD.tab_results.result_obj     ,
                                     result_obj_string                  ,
                                     self.RD.tab_results.datasetname    ,
                                     dname                              ,
                                     ))
    
    def SVMXcelRecord(self):
        svmrp = SVMReport(output_file  = self.svmoutput_file    ,
                          dset         = self.dset              ,
                          dsort        = self.dsort             ,
                          nameabr      = self.nameabr           ,
                          )
        svmrp.xcelRecord()
    
    def MSTATXcelRecord(self):
        mstatrp = MSTATReport(output_file  = self.mstatoutput_file  ,
                              pthreshold   = 0.08                   ,
                              dset         = self.dset              ,
                              dsort        = self.dsort             ,
                              nameabr      = self.nameabr           ,
                              )
        mstatrp.xcelRecord()
            
    def featureXcelRecord(self):
        ftdisplay = FeatureReport(output_file  = self.foutput_file  ,
                                  pthreshold   = 0.08               ,
                                  dset         = self.dset          ,
                                  dsort        = self.dsort         ,
                                  nameabr      = self.nameabr       ,
                                  )
        ftdisplay.xcelRecord()
        
    def formalXcelRecord(self):
        fm = FormalReport(output_file  = self.moutput_file  ,
                          pthreshold   = 0.08               ,
                          dset         = self.dset          ,
                          dsort        = self.dsort         ,
                          )
        fm.xcelRecord()
        
    def getAbrName(self,dset):
        #datasetname -> 'gutmtpslhpcr_Colon_Lumen__Crohn_Colon__Crohn__Lumen'
        a               = dset.find('_')
        nameM           = dset[a+1:] #'Colon_Lumen__Crohn_Colon__Crohn_Lumen'
        nameA           = nameM.split('__')[0] # 'Colon_Lumen'
        nameB           = '__'.join(nameM.split('__')[1:]) # 'Crohn_Colon__Crohn_Lumen'    
        nameBA          = nameB.split('__')[0] # 'Crohn_Colon'
        nameBB          = nameB.split('__')[1] # 'Crohn_Lumen'
        #Process nameA
        nameA_p         = nameA.split('_')
        nameA_p_list    = []
        for i in nameA_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameA_p_list.append(abr)
        nameA_abr       = '_'.join(nameA_p_list) #'C_LM'
        #Process nameBA
        nameBA_p        = nameBA.split('_')
        nameBA_p_list   = []
        for i in nameBA_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameBA_p_list.append(abr)
        nameBA_abr      = '_'.join(nameBA_p_list) #'C_CO'
        #Process nameBB
        nameBB_p        = nameBB.split('_')
        nameBB_p_list   = []
        for i in nameBB_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameBB_p_list.append(abr)
        nameBB_abr      = '_'.join(nameBB_p_list) #'C_LM'
        #Coming names
        nameAbr         = '__'.join([nameA_abr,nameBA_abr,nameBB_abr])
        return nameAbr
    
       
class GutMTPSDataPackage(DataPrePackage):
    
    def __init__(self,**kwargs):
        #Arbitrary properties
        self.Class_X        = None
        self.Class_1        = None
        self.__dict__.update(kwargs)
        DataPrePackage.__init__(self,**kwargs)


class INFO(object):
    
    def __init__(self,**kwargs):
        self.fullname       = ''
        self.dataoption     = Datap.Option.fix
        self.permremovelist = ['']
        self.keeplist       = []
        self.fixedcls       = ''
        self.matrix         = [ [], [] ]
        self.pair           = []
        #Arbitrary properties
        self.Class_X        = None
        self.Class_1        = None
        self.Length         = None
        self.__dict__.update(kwargs)
        


